/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package interact;

import entity.Server;
import GUI.frmLogin;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;

/**
 *
 * @author UnderGround
 */
public class connect {

    private static DefaultTableModel dtm;
    public static Connection conn;
    public static String Username, Password, url, databaseName, ServerName;
    public static int port;
    private static ResourceBundle rsb;
    frmLogin login;
    private static Statement st;
    private static ResultSet rs;
    public static ResultSetMetaData rsmd;
    private static CallableStatement call;

    public static Connection connection() {
        if (conn == null) {
            try {
                configServer();
                Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
                conn = DriverManager.getConnection(url, Username, Password);

            } catch (ClassNotFoundException ex) {
                System.out.println("connecting failure");
            } catch (SQLException ex) {
                System.out.println("connecting failure");
            }
        }
        return conn;
    }

    public static void configServer() {
        FileInputStream fi = null;
        ObjectInputStream oi = null;
        try {
            fi = new FileInputStream("server.config");
            oi = new ObjectInputStream(fi);
            if (oi != null) {

                Server sv = (Server) oi.readObject();
                databaseName = sv.getDatabaseName();
                ServerName = sv.getServerName();
                port = sv.getPort();
                Username = sv.getUserName();
                Password = sv.getPassword();
                url = "jdbc:sqlserver://" + ServerName + ":" + port + ";databaseName=" + databaseName;
            } else {
                System.out.println("error occur when connection database!");
            }
            oi.close();
            fi.close();

        } catch (IOException ex) {
            System.out.println("not found 'server.config' file");
        } catch (ClassNotFoundException ex) {
            System.out.println("not found 'server.config' file");
        } catch (Exception ex) {
            System.out.println("not found 'server.config' file");
        }
    }

    public static boolean execute(String sql) {
        try {
            st = conn.createStatement();
            st.execute(sql);

        } catch (SQLException ex) {
            Logger.getLogger(connect.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
        return true;
    }

    public static ResultSet createResultSet(String sql) {
        try {
            st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            rs = st.executeQuery(sql);
        } catch (SQLException ex) {
            Logger.getLogger(connect.class.getName()).log(Level.SEVERE, null, ex);
        }

        return rs;
    }

     public static ResultSet createResultSet2(String proc) {
        try {
             call = conn.prepareCall("{call "+proc+"(?,?,?)}");
            rs = call.executeQuery();
        } catch (SQLException ex) {
            Logger.getLogger(connect.class.getName()).log(Level.SEVERE, null, ex);
        }

        return rs;
    }
    public static void loadDataTable(String sql, JTable table) {
        try {
            dtm = (DefaultTableModel) table.getModel();
            for (int i = dtm.getRowCount() - 1; i >= 0; i--) {
                dtm.removeRow(i);
            }
            Vector vtCols = new Vector();
            rs = createResultSet(sql);
            rsmd = rs.getMetaData();
            for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                vtCols.add(rsmd.getColumnName(i));
            }
            dtm.setColumnIdentifiers(vtCols);
            while (rs.next()) {
                Vector vtRow = new Vector();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    vtRow.add(rs.getString(i));
                }
                dtm.addRow(vtRow);
            }
            table.setModel(dtm);

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public static void loadDataTable2(ResultSet rs, JTable table) {
        try {
            dtm = (DefaultTableModel) table.getModel();
            for (int i = dtm.getRowCount() - 1; i >= 0; i--) {
                dtm.removeRow(i);
            }
            Vector vtCols = new Vector();
            rsmd = rs.getMetaData();
            for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                vtCols.add(rsmd.getColumnName(i));
            }
            dtm.setColumnIdentifiers(vtCols);
            while (rs.next()) {
                Vector vtRow = new Vector();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    vtRow.add(rs.getString(i));
                }
                dtm.addRow(vtRow);
            }
            table.setModel(dtm);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public static DefaultTableModel loadDataTable3(String procName, JTable table) {
        try {
            dtm = (DefaultTableModel) table.getModel();
            for (int i = dtm.getRowCount() - 1; i >= 0; i--) {
                dtm.removeRow(i);
            }
            Vector vtCols = new Vector();
            try {
                call = conn.prepareCall("{call " + procName + "()}");
                rs = call.executeQuery();
            } catch (SQLException ex) {
                Logger.getLogger(DataInteraction.class.getName()).log(Level.SEVERE, null, ex);
            }

            rsmd = rs.getMetaData();
            for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                vtCols.add(rsmd.getColumnName(i));
            }
            dtm.setColumnIdentifiers(vtCols);
            while (rs.next()) {
                Vector vtRow = new Vector();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    vtRow.add(rs.getString(i));
                }
                dtm.addRow(vtRow);
            }
            table.setModel(dtm);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return dtm;
    }

    public static DefaultTableModel loadDataToTableListBorrower(String procName, JTable table, int BookID) {
        try {
            dtm = (DefaultTableModel) table.getModel();
            for (int i = dtm.getRowCount() - 1; i >= 0; i--) {
                dtm.removeRow(i);
            }
            Vector vtCols = new Vector();
            try {
                call = conn.prepareCall("{call " + procName + "(?)}");
                call.setInt(1, BookID);
                rs = call.executeQuery();
            } catch (SQLException ex) {
                Logger.getLogger(DataInteraction.class.getName()).log(Level.SEVERE, null, ex);
            }

            rsmd = rs.getMetaData();
            for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                vtCols.add(rsmd.getColumnName(i));
            }
            dtm.setColumnIdentifiers(vtCols);
            while (rs.next()) {
                Vector vtRow = new Vector();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    vtRow.add(rs.getString(i));
                }
                dtm.addRow(vtRow);
            }
            table.setModel(dtm);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return dtm;
    }
}
